﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Common
{
    /// <summary>
    /// excel 工具类
    /// </summary>
    public class ExcelTool
    {
        private Dictionary<string, string> paramList;

        /// <summary>
        /// 读取参数
        /// </summary>
        /// <param name="key">参数名</param>
        /// <returns>参数值</returns>
        /// <exception cref="Exception"></exception>
        public string GetParams(string key)
        {
            if (paramList.Keys.Contains(key))
            {
                return paramList[key];
            }
            throw new Exception($"未找到[{key}]配置项");
        }

        /// <summary>
        /// 配置参数
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        public void SetParams(string key, string value)
        {
            if (paramList.Keys.Contains(key))
            {
                paramList[key] = value;
            }
            else
            {
                paramList.Add(key, value);
            }
        }

        /// <summary>
        /// 解析文件
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <param name="sheetName">sheet名称</param>
        /// <exception cref="FileNotFoundException"></exception>
        public void ParseFile(string path, string sheetName)
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            if (!File.Exists(path))
            {
                throw new FileNotFoundException(path);
            }
            using (ExcelPackage package = new ExcelPackage(path))
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets[sheetName];
                paramList = new Dictionary<string, string>();
                int row = 1;
                while (true)
                {
                    ExcelRangeBase cell1 = sheet.Cells[row, 1];
                    ExcelRangeBase cell2 = sheet.Cells[row, 2];
                    if (cell1.Value == null)
                    {
                        break;
                    }
                    if (string.IsNullOrEmpty(cell1.Value.ToString().Trim(' ')))
                    {
                        break;
                    }
                    string key = cell1.Value.ToString().Trim(' ');

                    string value = cell2.Value.ToString().Trim(' ');

                    paramList.Add(key, value);
                    row++;
                }
            }
        }

        /// <summary>
        /// 保存配置到文件
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <param name="sheetName">sheet名</param>
        /// <exception cref="FileNotFoundException"></exception>
        public void SaveToFile(string path, string sheetName)
        {
            if (paramList is null)
            {
                throw new ArgumentNullException(nameof(paramList));
            }
            if (!File.Exists(path))
            {
                throw new FileNotFoundException(path);
            }

            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            using (ExcelPackage package = new ExcelPackage(path))
            {
                ExcelWorksheet sheet = package.Workbook.Worksheets[sheetName];

                for (int row = 0; row < paramList.Count; row++)
                {
                    string key = sheet.Cells[row + 1, 1].Value.ToString();
                    sheet.Cells[row + 1, 2].Value = paramList[key];
                }
                package.Save();
            }
        }
    }
}